﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.IO;
using System.Collections.Generic;
using System.Collections;

/// <summary>
///DB 的摘要说明
/// </summary>
public class DB
{
    private SqlConnection connection = null;
	public DB()
	{
        connection = DBHelper.getConnection();
        if (connection.State == ConnectionState.Closed)
        {
            connection.Open();
        }
	}


    /// <summary>
    /// 创建新的类别
    /// </summary>
    /// <param name="name_cn">类别中文名</param>
    /// <param name="name_en">类别英文名</param>
    /// <param name="belong">所属类别的id</param>
    public void addCategory(string name_cn,string name_en,int belong)
    {
        string sql = "insert into category(categoryName_en,categoryName_cn,categoryBelongTo) values('" + name_en + "','" + name_cn + "'," + belong + ")";
        SqlCommand command = connection.CreateCommand();
        command.CommandText = sql;
        command.ExecuteNonQuery();
    }

    /// <summary>
    /// 获取大类
    /// </summary>
    /// <returns>返回已经存在的大类</returns>
    public DataSet getBigCategory()
    {
        SqlCommand command = connection.CreateCommand();
        command.CommandText = "select * from category where categoryBelongTo=-1";
        DataSet ds = new DataSet();
        SqlDataAdapter sda = new SqlDataAdapter();
        sda.SelectCommand = command;
        sda.Fill(ds);
        return ds;
    }

    /// <summary>
    /// 获取所有全部的分类
    /// </summary>
    /// <returns></returns>
    public DataSet getAllCategory()
    {
        SqlCommand command = connection.CreateCommand();
        command.CommandText = "(select c.categoryId,c.categoryName_en,c.categoryName_cn,c.categoryDate,c.categoryBelongTo,d.categoryName_cn as belongName from category as c,category as d where c.categoryBelongTo = d.categoryId) union (select *,'无' as belongName from category where categoryBelongTo = -1) order by belongName";
        DataSet ds = new DataSet();
        SqlDataAdapter sda = new SqlDataAdapter();
        sda.SelectCommand = command;
        sda.Fill(ds);
        return ds;
    }

    /// <summary>
    /// 获取小类别
    /// </summary>
    /// <returns>返回全部小类别的中文名</returns>
    public DataSet getSmallCategory()
    {
        SqlCommand command = connection.CreateCommand();
        command.CommandText = "select distinct categoryId,categoryName_cn from category where categoryBelongTo != -1";
        DataSet ds = new DataSet();
        SqlDataAdapter sda = new SqlDataAdapter();
        sda.SelectCommand = command;
        sda.Fill(ds);
        return ds;
    }

    /// <summary>
    /// 获取某一个分类的子类
    /// </summary>
    /// <param name="categoryId">大分类的ID</param>
    /// <returns></returns>
    public DataSet getSubCategory(int categoryId)
    {
        SqlCommand command = connection.CreateCommand();
        command.CommandText = "select categoryId,categoryName_cn,categoryName_en from category where categoryBelongTo = @cid";
        command.Parameters.AddWithValue("@cid",categoryId);
        DataSet ds = new DataSet();
        SqlDataAdapter sda = new SqlDataAdapter();
        sda.SelectCommand = command;
        sda.Fill(ds);
        return ds;
    }


    /// <summary>
    /// 添加产品
    /// </summary>
    /// <param name="name_cn">产品中文名</param>
    /// <param name="name_en">产品英文名</param>
    /// <param name="desc_cn">产品中文描述</param>
    /// <param name="desc_en">产品英文描述</param>
    /// <param name="image">上传的图片的名称</param>
    /// <param name="isRecommended">是否是推荐产品</param>
    /// <param name="isNew">是否是新产品</param>
    /// <param name="belongTo">所属的类别的id</param>
    public void addProduct(string name_cn,string name_en,string desc_cn,string desc_en,string image,int isRecommended,int isNew,int belongTo)
    {
        string sql = "insert into products(productName_en,productName_cn,productDesc_en,productDesc_cn,productImage,productRecommended,productNew,productCategory)"
        +" values(@name_en,@name_cn,@desc_en,@desc_cn,@pimage,@isRecommended,@isNew,@belongTo)";
        SqlParameter[] paramArray = new SqlParameter[]{
            new SqlParameter("@name_en",name_en.Trim()),
            new SqlParameter("@name_cn",name_cn.Trim()),
            new SqlParameter("@desc_en",desc_en.Trim()),
            new SqlParameter("@desc_cn",desc_cn.Trim()),
            new SqlParameter("@pimage",image),
            new SqlParameter("@isRecommended",isRecommended),
            new SqlParameter("@isNew",isNew),
            new SqlParameter("@belongTo",belongTo)
        };
        SqlCommand command = connection.CreateCommand();
        command.CommandText = sql;
        command.Parameters.AddRange(paramArray);
        command.ExecuteNonQuery();
    }

    /// <summary>
    /// 删除指定productId的产品信息(包含产品图片等相关信息)
    /// </summary>
    /// <param name="productId">产品的id</param>
    public void deleteProduct(int productId)
    {
        string sql = "select * from products where productId = @pid";
        SqlCommand command = new SqlCommand();
        command.CommandText = sql;
        command.Parameters.AddWithValue("@pid",productId);
        SqlDataAdapter sda = new SqlDataAdapter();
        sda.SelectCommand = command;
        DataTable dt = new DataTable();
        sda.Fill(dt);
        foreach (DataRow dr in dt.Rows)
        {
            string fileName = Convert.ToString(dr["productImage"]);
            File.Delete(Utils.getCurrentDirectory()+"\\pimages"+fileName);
            SqlCommand temp = connection.CreateCommand();
            string s = "delete from products where productId = @pid";
            temp.CommandText = s;
            temp.Parameters.AddWithValue("@pid",Convert.ToInt32(dr["productId"]));
            temp.ExecuteNonQuery();
        }
    }

    /// <summary>
    /// 获取推荐产品
    /// </summary>
    /// <returns>返回推荐产品的列表</returns>
    public DataSet getRecommendedProducts()
    {
        string sql = "select productId,productName_cn,productImage,productDate,productRecommended,categoryName_cn from products,category where products.productCategory = category.categoryId and productRecommended = 1";

        SqlCommand command = connection.CreateCommand();
        command.CommandText = sql;
        DataSet ds = new DataSet();
        SqlDataAdapter sda = new SqlDataAdapter();
        sda.SelectCommand = command;
        sda.Fill(ds);
        return ds;
    }

    /// <summary>
    /// 取消推荐产品
    /// </summary>
    /// <param name="productId">产品ID</param>
    public void cancelRecommended(int productId)
    {
        string sql = "update products set productRecommended = 0 where productId = @pid";
        SqlCommand command = connection.CreateCommand();
        command.CommandText = sql;
        command.Parameters.AddWithValue("@pid",productId);
        command.ExecuteNonQuery();
    }
    /// <summary>
    /// 获取为非推荐状态的产品列表
    /// </summary>
    /// <returns></returns>
    public DataSet getNotRecommendedProducts()
    {
        string sql = "select productId,productName_cn,productImage,productDate,productRecommended,categoryId,categoryName_cn from products,category where products.productCategory = category.categoryId and productRecommended = 0";

        SqlCommand command = connection.CreateCommand();
        command.CommandText = sql;
        DataSet ds = new DataSet();
        SqlDataAdapter sda = new SqlDataAdapter();
        sda.SelectCommand = command;
        sda.Fill(ds);
        return ds;
    }

    /// <summary>
    /// 将产品置为推荐状态
    /// </summary>
    /// <param name="productId">产品ID</param>
    public void recommend(int productId)
    {
        string sql = "update products set productRecommended = 1 where productId = @pid";
        SqlCommand command = connection.CreateCommand();
        command.CommandText = sql;
        command.Parameters.AddWithValue("@pid", productId);
        command.ExecuteNonQuery();
    }

    /// <summary>
    /// 获取最新产品
    /// </summary>
    /// <returns>返回最新产品列表</returns>
    public DataSet getNewProducts()
    {
        string sql = "select productId,productName_cn,productImage,productDate,productRecommended,categoryName_cn from products,category where products.productCategory = category.categoryId and productNew = 1";

        SqlCommand command = connection.CreateCommand();
        command.CommandText = sql;
        DataSet ds = new DataSet();
        SqlDataAdapter sda = new SqlDataAdapter();
        sda.SelectCommand = command;
        sda.Fill(ds);
        return ds;
    }

    /// <summary>
    /// 取消产品为最新产品状态
    /// </summary>
    /// <param name="productId">产品id</param>
    public void cancelNew(int productId)
    {
        string sql = "update products set productNew = 0 where productId = @pid";
        SqlCommand command = connection.CreateCommand();
        command.CommandText = sql;
        command.Parameters.AddWithValue("@pid", productId);
        command.ExecuteNonQuery();
    }

    /// <summary>
    /// 获取不是最新产品的产品
    /// </summary>
    /// <returns>返回不是最新产品的产品列表</returns>
    public DataSet getNotNewProducts()
    {
        string sql = "select productId,productName_cn,productImage,productDate,productRecommended,categoryName_cn,productDate from products,category where products.productCategory = category.categoryId and productNew = 0";

        SqlCommand command = connection.CreateCommand();
        command.CommandText = sql;
        DataSet ds = new DataSet();
        SqlDataAdapter sda = new SqlDataAdapter();
        sda.SelectCommand = command;
        sda.Fill(ds);
        return ds;
    }

    /// <summary>
    /// 将产品置为最新状态
    /// </summary>
    /// <param name="productId">产品Id</param>
    public void dimNew(int productId)
    {
        string sql = "update products set productNew = 1 where productId = @pid";
        SqlCommand command = connection.CreateCommand();
        command.CommandText = sql;
        command.Parameters.AddWithValue("@pid", productId);
        command.ExecuteNonQuery();
    }

    /// <summary>
    /// 返回相应的ID的产品信息
    /// </summary>
    /// <param name="productId">产品ID</param>
    /// <returns></returns>
    public Product getProduct(int productId)
    {
        string sql = "select * from products where productId = @pid ";
        SqlCommand command = connection.CreateCommand();
        command.CommandText = sql;
        command.Parameters.AddWithValue("@pid",productId);
        SqlDataAdapter sda = new SqlDataAdapter();
        sda.SelectCommand = command;
        DataTable dt = new DataTable();
        sda.Fill(dt);
        Product product = null;
        foreach (DataRow dr in dt.Rows)
        {
            product = new Product();
            product.ProductId = Convert.ToInt32(dr["productId"]);
            product.Name_cn = Convert.ToString(dr["productName_cn"]);
            product.Name_en = Convert.ToString(dr["productName_en"]);
            product.Desc_cn = Convert.ToString(dr["productDesc_cn"]);
            product.Desc_en = Convert.ToString(dr["productDesc_en"]);
            product.Image = Convert.ToString(dr["productImage"]);
            product.Recommended = Convert.ToInt32(dr["productRecommended"]) == 1?true:false;
            product.DimNew = Convert.ToInt32(dr["productNew"]) == 1?true:false;
            product.Category = Convert.ToInt32(dr["productCategory"]);
            product.Date = Convert.ToDateTime(dr["productDate"]);
        }
        return product;
    }

    /// <summary>
    /// 删除相应的分类(会一并删除此分类下所有的产品,包括产品图片之类的一切相关信息)
    /// </summary>
    /// <param name="categoryId">分类的ID</param>
    public void deleteCategory(int categoryId)
    {
        string sql = "select * from category where categoryId = @cid";
        SqlCommand command = new SqlCommand();
        command.CommandText = sql;
        SqlDataAdapter sda = new SqlDataAdapter();
        sda.SelectCommand = command;
        command.Parameters.AddWithValue("@cid",categoryId);
        DataTable dt = new DataTable();
        sda.Fill(dt);
        foreach (DataRow dr in dt.Rows)
        {
            int cid = Convert.ToInt32(dr["categoryId"]);
            int belong = Convert.ToInt32(dr["categoryBelongTo"]);
            if (belong == -1)
            {
                deleteOuterCategory(cid);
            }
            else
            {
                deleteInnerCategory(cid);
            }
        }
    }

    /// <summary>
    /// 删除小类别及其中的产品
    /// </summary>
    /// <param name="categoryId">小类别的ID</param>
    private void deleteInnerCategory(int categoryId)
    {
        string sql = "select * from products where productCategory = @cid";
        SqlCommand command = new SqlCommand();
        command.CommandText = sql;
        command.Parameters.AddWithValue("@cid",categoryId);
        DataTable dt = new DataTable();
        SqlDataAdapter sda = new SqlDataAdapter();
        sda.SelectCommand = command;
        sda.Fill(dt);
        foreach (DataRow dr in dt.Rows)
        {
            int s = Convert.ToInt32(dr["productId"]);
            deleteProduct(s);
        }

    }

    /// <summary>
    /// 删除大类别及其下所有的小类别
    /// </summary>
    /// <param name="categoryId">大类别的类别ID</param>
    private void deleteOuterCategory(int categoryId)
    {
        string sql = "select * from category where categoryBelongTo = @cid";
        SqlCommand command = new SqlCommand();
        command.CommandText = sql;
        SqlDataAdapter sda = new SqlDataAdapter();
        sda.SelectCommand = command;
        DataTable dt = new DataTable();
        sda.Fill(dt);
        foreach (DataRow dr in dt.Rows)
        {
            int tempCategoryId = Convert.ToInt32(dr["categoryId"]);
            deleteInnerCategory(tempCategoryId);
        }
    }


    /// <summary>
    /// 获取某个特定id的类别的信息
    /// </summary>
    /// <param name="categoryId">类别ID</param>
    /// <returns></returns>
    public Category getCategory(int categoryId)
    {
        string sql = "select * from category where categoryId = @cid";
        SqlCommand command = connection.CreateCommand();
        command.CommandText = sql;
        command.Parameters.AddWithValue("@cid",categoryId);
        SqlDataAdapter sda = new SqlDataAdapter();
        DataTable dt = new DataTable();
        sda.SelectCommand = command;
        sda.Fill(dt);
        Category category = null;
        foreach(DataRow dr in dt.Rows)
        {
            category = new Category();
            category.CategoryId = Convert.ToInt32(dr["categoryId"]);
            category.Name_cn = Convert.ToString(dr["categoryName_cn"]);
            category.Name_en = Convert.ToString(dr["categoryName_en"]);
            category.BelongTo = Convert.ToInt32(dr["categoryBelongTo"]);
            category.CategoryDate = Convert.ToDateTime(dr["categoryDate"]);
        }
        return category;
    }

    /// <summary>
    /// 修改分类信息
    /// </summary>
    /// <param name="categoryId">类别的ID</param>
    /// <param name="name_cn">类别的中文名</param>
    /// <param name="name_en">类别的英文名</param>
    /// <param name="belongTo">类别所属的类别</param>
    public void modifyCategory(int categoryId,string name_cn,string name_en,int belongTo)
    {
        string sql = "update category set categoryName_cn = @name_cn,categoryName_en = @name_en,categoryBelongTo = @belongTo where categoryId = @cid";
        SqlCommand command = connection.CreateCommand();
        command.CommandText = sql;
        command.Parameters.AddWithValue("@name_cn",name_cn);
        command.Parameters.AddWithValue("@name_en",name_en);
        command.Parameters.AddWithValue("@belongTo",belongTo);
        command.Parameters.AddWithValue("@cid",categoryId);
        command.ExecuteNonQuery();
    }

    /// <summary>
    /// 获取当前所有的产品
    /// </summary>
    /// <returns>返回dataset</returns>
    public DataSet getAllProducts()
    {
        string sql = "select * from products inner join category on products.productCategory = category.categoryId order by products.productCategory";
        SqlCommand command = connection.CreateCommand();
        command.CommandText = sql;
        SqlDataAdapter sda = new SqlDataAdapter();
        sda.SelectCommand = command;
        DataSet ds = new DataSet();
        sda.Fill(ds);
        return ds;
    }


    /// <summary>
    /// 更改产品信息
    /// </summary>
    /// <param name="productId">要修改的产品ID</param>
    /// <param name="name_cn">产品的中文名</param>
    /// <param name="name_en">产品的英文名</param>
    /// <param name="desc_cn">产品的中文描述</param>
    /// <param name="desc_en">产品的英文描述</param>
    /// <param name="isRecommended">是否推荐</param>
    /// <param name="isNew">是否最新</param>
    /// <param name="category">产品分类</param>
    /// <param name="image">图片的文件名</param>
    public void modifyProduct(int productId,string name_cn,string name_en,string desc_cn,string desc_en,int isRecommended,int isNew,int category,string image)
    {
        string sql = "update products set productName_en = @name_en,productName_cn = @name_cn,productDesc_en = @desc_en,productDesc_cn = @desc_cn,productImage = @pimage,productRecommended = @recommended,productNew = @pnew,productCategory = @category where productId = @pid";
        SqlCommand command = connection.CreateCommand();
        command.CommandText = sql;
        command.Parameters.AddWithValue("@name_en",name_en);
        command.Parameters.AddWithValue("@name_cn",name_cn);
        command.Parameters.AddWithValue("@desc_en",desc_en);
        command.Parameters.AddWithValue("@desc_cn",desc_cn);
        command.Parameters.AddWithValue("@pimage",image);
        command.Parameters.AddWithValue("@recommended",isRecommended);
        command.Parameters.AddWithValue("@pnew",isNew);
        command.Parameters.AddWithValue("@category",category);
        command.Parameters.AddWithValue("@pid",productId);
        command.ExecuteNonQuery();
    }

    //获取全部新闻
    public DataSet getAllNews()
    {
        string sql = "select * from news order by newsDate desc";
        SqlCommand command = connection.CreateCommand();
        command.CommandText = sql;
        SqlDataAdapter sda = new SqlDataAdapter();
        sda.SelectCommand = command;
        DataSet ds = new DataSet();
        sda.Fill(ds);
        return ds;
    }

    /// <summary>
    /// 发布新闻
    /// </summary>
    /// <param name="content_cn">中文内容</param>
    /// <param name="content_en">英文内容</param>
    public void addNews(string content_cn, string content_en)
    {
        string sql = "insert into news(newsContent_cn,newsContent_en) values(@content_cn,@content_en)";
        SqlCommand command = connection.CreateCommand();
        command.CommandText = sql;
        command.Parameters.AddWithValue("@content_cn",content_cn);
        command.Parameters.AddWithValue("@content_en",content_en);
        command.ExecuteNonQuery();
    }
    
    /// <summary>
    /// 获取某一条新闻
    /// </summary>
    /// <param name="newsId">新闻的ID</param>
    /// <returns></returns>
    public News getNews(int newsId)
    {
        string sql = "select * from news where newsId = @nid";
        SqlCommand command = connection.CreateCommand();
        command.CommandText = sql;
        command.Parameters.AddWithValue("@nid",newsId);
        SqlDataAdapter sda = new SqlDataAdapter();
        sda.SelectCommand = command;
        DataTable dt = new DataTable();
        sda.Fill(dt);
        News news = null;
        foreach (DataRow dr in dt.Rows)
        {
            news = new News();
            news.NewsId = Convert.ToInt32(dr["newsId"]);
            news.Content_cn = Convert.ToString(dr["newsContent_cn"]);
            news.Content_en = Convert.ToString(dr["newsContent_en"]);
            news.PublishDate = Convert.ToDateTime(dr["newsDate"]);
        }
        return news;
    }

    /// <summary>
    /// 删除某一条新闻
    /// </summary>
    /// <param name="newsId">该新闻的ID</param>
    public void deleteNews(int newsId)
    {
        string sql = "delete from news where newsId = @nid";
        SqlCommand command = connection.CreateCommand();
        command.CommandText = sql;
        command.Parameters.AddWithValue("@nid",newsId);
        command.ExecuteNonQuery();
    }

    /// <summary>
    /// 用户登录
    /// </summary>
    /// <param name="userName">用户名</param>
    /// <param name="password">密码</param>
    /// <returns>如果找到了用户，并登录成功，则返回该用户id,否则返回-1</returns>
    public int login(string userName, string password)
    {
        string sql = "select * from users where userName = @name and password = @password";
        SqlCommand command = connection.CreateCommand();
        command.CommandText = sql;
        command.Parameters.AddWithValue("@name",userName);
        command.Parameters.AddWithValue("@password",password);
        SqlDataAdapter sda = new SqlDataAdapter();
        sda.SelectCommand = command;
        DataTable dt = new DataTable();
        sda.Fill(dt);
        int number = dt.Rows.Count;
        if (number == 1)
            return Convert.ToInt32(dt.Rows[0]["userId"]);
        else
            return -1;

    }

    /// <summary>
    /// 根据用户ID获取用户信息
    /// </summary>
    /// <param name="userId">用户id</param>
    /// <returns></returns>
    public Manager getManager(int userId)
    {
        Manager manager = null;
        string sql = "select * from users where userId = @uid";
        SqlCommand command = connection.CreateCommand();
        command.CommandText = sql;
        command.Parameters.AddWithValue("@uid",userId);
        SqlDataAdapter sda = new SqlDataAdapter();
        sda.SelectCommand = command;
        DataTable dt = new DataTable();
        sda.Fill(dt);
        if (dt.Rows.Count == 1)
        {
            manager = new Manager();
            manager.UserId = userId;
            manager.UserName = Convert.ToString(dt.Rows[0]["userName"]);
            manager.UserPassword = Convert.ToString(dt.Rows[0]["password"]);
            manager.LastLogin = Convert.ToDateTime(dt.Rows[0]["lastLogin"]);
            //更新登陆时间
            sql = "update users set lastLogin=getdate()";
            command.CommandText = sql;
            command.ExecuteNonQuery();
        }
        return manager;
    }

    /// <summary>
    /// 用于在从来没有输入过联系方式的情况下，进行联系方式的插入
    /// </summary>
    /// <param name="contact">联系方式对象</param>
    private void addContact(Contact contact)
    {
        string sql = "insert into contact(address_cn,address_en,postcode,email) values(@add_cn,@add_en,@postcode,@email)";
        SqlCommand command = connection.CreateCommand();
        command.CommandText = sql;
        command.Parameters.AddWithValue("@add_cn",contact.Address_cn);
        command.Parameters.AddWithValue("@add_en",contact.Address_en);
        command.Parameters.AddWithValue("@postcode",contact.PostCode);
        command.Parameters.AddWithValue("@email",contact.Email);
        command.ExecuteNonQuery();

        //之后取得这个已存在的id
        SqlDataAdapter sda = new SqlDataAdapter();
        sql = "select * from contact";
        command = connection.CreateCommand();
        command.CommandText = sql;
        sda.SelectCommand = command;
        DataTable dt = new DataTable();
        sda.Fill(dt);
        foreach(DataRow dr in dt.Rows)
        {
            contact.ContactId = Convert.ToInt32(dr["contactId"]);
        }

        //之后把电话列表和传真列表插入进去
        addPhoneAndFax(contact,contact.ContactId);
    }

    /// <summary>
    /// 管理员修改联系方式,如果以前 没有联系方式，就先增加，有了之后就修改
    /// </summary>
    /// <param name="contact">参数太多，所以使用了对象进行传递</param>
    public void modifyContact(Contact contact)
    {
        //先判断有没有之前录入过联系方式
        string sql = "select * from contact";
        SqlCommand command = connection.CreateCommand();
        command.CommandText = sql;
        SqlDataAdapter sda = new SqlDataAdapter();
        sda.SelectCommand = command;
        DataTable dt = new DataTable();
        sda.Fill(dt);
        if (dt.Rows.Count == 0)
        {
            addContact(contact);
        }


        //首先更新contact表中的内容
        sql = "update contact set address_cn = @a_cn,address_en = @a_en,postcode = @pcode,email = @email where contactId=@contactId";
        command = connection.CreateCommand();
        command.CommandText = sql;
        command.Parameters.AddWithValue("@a_cn",contact.Address_cn);
        command.Parameters.AddWithValue("a_en",contact.Address_en);
        command.Parameters.AddWithValue("@pcode",contact.PostCode);
        command.Parameters.AddWithValue("@email",contact.Email);
        command.Parameters.AddWithValue("@contactId",contact.ContactId);
        command.ExecuteNonQuery();
        //再更新一下phone表
        addPhoneAndFax(contact,contact.ContactId);
    }

    private void addPhoneAndFax(Contact contact,int contactId)
    {
        //先把以前的电话号码给删除了
        string sql = "delete from phone where contactId = @cid";
        SqlCommand command = connection.CreateCommand();
        command.CommandText = sql;
        command.Parameters.AddWithValue("@cid",contactId);
        command.ExecuteNonQuery();

        //再把以前的传真号码给删除了
        sql = "delete from fax where contactId = @cid";
        command = connection.CreateCommand();
        command.CommandText = sql;
        command.Parameters.AddWithValue("@cid",contactId);
        command.ExecuteNonQuery();

        //插入电话号码
         List<string> phoneList = contact.PhoneNumber;
         string insertPhone = "insert into phone(contactId,number) values(@cid,@num)";
        for (int i = 0; i < phoneList.Count; ++i)
        {
            string phone = phoneList[i];
            SqlCommand temp = connection.CreateCommand();
            temp.CommandText = insertPhone;
            temp.Parameters.AddWithValue("@cid",contactId);
            temp.Parameters.AddWithValue("@num",phone);
            temp.ExecuteNonQuery();
        }

        //插入传真
        List<string> faxList = contact.Fax;
        string insertFax = "insert into fax(contactId,number) values(@cid,@num)";
        for (int j = 0; j < faxList.Count; ++j)
        {
            string fax = faxList[j];
            SqlCommand sc = connection.CreateCommand();
            sc.CommandText = insertFax;
            sc.Parameters.AddWithValue("@cid",contactId);
            sc.Parameters.AddWithValue("@num",fax);
            sc.ExecuteNonQuery();
        }
    }

    /// <summary>
    /// 获取联系方式，由于只有一个联系地址，顾这里参数不起作用
    /// </summary>
    /// <param name="contactId">联系地址的id</param>
    /// <returns></returns>
    public Contact getContact(int contactId)
    {
        //先取出总地址
        Contact contact = null;
        string sql = "select * from contact";
        SqlCommand command = connection.CreateCommand();
        command.CommandText = sql;
        SqlDataAdapter sda = new SqlDataAdapter();
        sda.SelectCommand = command;
        DataTable dt = new DataTable();
        sda.Fill(dt);
        foreach (DataRow dr in dt.Rows)
        {
            contact = new Contact();
            contact.ContactId = Convert.ToInt32(dr["contactId"]);
            contact.Address_cn = Convert.ToString(dr["address_cn"]);
            contact.Address_en = Convert.ToString(dr["address_en"]);
            contact.PostCode = Convert.ToInt32(dr["postcode"]);
            contact.Email = Convert.ToString(dr["email"]);
        }

        //判断，是否产生了contact对象，不然会出现空指针异常
        if (contact != null)
        {
            //查询相关的电话号码
            sql = "select * from phone where contactId = @contactId";
            command = connection.CreateCommand();
            command.CommandText = sql;
            command.Parameters.AddWithValue("@contactId",contact.ContactId);
            sda = new SqlDataAdapter();
            sda.SelectCommand = command;
            dt = new DataTable();
            sda.Fill(dt);
            contact.PhoneNumber = new List<string>();
            foreach (DataRow drr in dt.Rows)
            {
                contact.PhoneNumber.Add(Convert.ToString(drr["number"]));
            }

            //查询相关的传真号码
            sql = "select * from fax where contactId = @contactId";
            command = connection.CreateCommand();
            command.CommandText = sql;
            command.Parameters.AddWithValue("@contactId",contact.ContactId);
            sda = new SqlDataAdapter();
            sda.SelectCommand = command;
            dt = new DataTable();
            sda.Fill(dt);
            contact.Fax = new List<string>();
            foreach (DataRow drrrr in dt.Rows)
            {
                contact.Fax.Add(Convert.ToString(drrrr["number"]));
            }
        }

        return contact;
    }

    /// <summary>
    /// 获取推荐产品的图片的地址url信息
    /// </summary>
    /// <returns>返回(id,valule)键值对</returns>
    public Hashtable getRecommendedProductsImages()
    {
        SqlCommand command = connection.CreateCommand();
        string sql = "select productId,productImage from products where productRecommended = 1";
        SqlDataAdapter sda = new SqlDataAdapter();
        command.CommandText = sql;
        sda.SelectCommand = command;
        DataTable dt = new DataTable();
        sda.Fill(dt);
        Hashtable table = new Hashtable();
        foreach (DataRow dr in dt.Rows)
        {
            table.Add(dr["productId"],dr["productImage"]);
        }
        return table;
    }

    /// <summary>
    /// 获取最新产品的图片地址url信息
    /// </summary>
    /// <returns>返回(id,value)键值对</returns>
    public Hashtable getNewProductsImages()
    {
        SqlCommand command = connection.CreateCommand();
        string sql = "select productId,productImage from products where productNew = 1";
        SqlDataAdapter sda = new SqlDataAdapter();
        command.CommandText = sql;
        sda.SelectCommand = command;
        DataTable dt = new DataTable();
        sda.Fill(dt);
        Hashtable table = new Hashtable();
        foreach (DataRow dr in dt.Rows)
        {
            table.Add(dr["productId"], dr["productImage"]);
        }
        return table;
    }

    /// <summary>
    /// 获取某一类下面的产品
    /// </summary>
    /// <param name="categoryId">类别id</param>
    /// <returns>返回某一类别下的所有产品</returns>
    public DataSet getProductsByCategory(int categoryId)
    {
        string sql = "select productId,productName_en,productName_cn,productImage,productDate,categoryId,categoryName_cn from products,category where productCategory = @cid and productCategory = category.categoryId order by productDate desc";
        SqlCommand command = connection.CreateCommand();
        command.CommandText = sql;
        command.Parameters.AddWithValue("@cid",categoryId);
        SqlDataAdapter sda = new SqlDataAdapter();
        sda.SelectCommand = command;
        DataSet ds = new DataSet();
        sda.Fill(ds);
        return ds;
    }

    /// <summary>
    /// 修改公司简介
    /// </summary>
    /// <param name="intro_cn">中文简介</param>
    /// <param name="intro_en">英文简介</param>
    public void modifyIntroduction(string intro_cn,string intro_en)
    {
        string sql = "update introduction set introduction_cn = @intro_cn,introduction_en = @intro_en";
        SqlCommand command = connection.CreateCommand();
        command.CommandText = sql;
        command.Parameters.AddWithValue("@intro_cn",intro_cn);
        command.Parameters.AddWithValue("@intro_en",intro_en);
        command.ExecuteNonQuery();
    }

    public Introduction getIntroduction() 
    {
        string sql = "select * from introduction";
        SqlCommand command = connection.CreateCommand();
        command.CommandText = sql;
        DataTable dt = new DataTable();
        SqlDataAdapter sda = new SqlDataAdapter();
        sda.SelectCommand = command;
        sda.Fill(dt);
        Introduction intro = null;
        foreach (DataRow dr in dt.Rows)
        {
            intro = new Introduction();
            intro.InfoId = Convert.ToInt32(dr["infoId"]);
            intro.Intro_cn = Convert.ToString(dr["introduction_cn"]);
            intro.Intro_en = Convert.ToString(dr["introduction_en"]);
        }
        return intro;
    }

    public DataSet search(string key)
    {
        string sql = "select * from products where productName_cn like @key or productName_en like @key or productDesc_cn like @key or productDesc_en like @key";
        SqlCommand command = connection.CreateCommand();
        command.CommandText = sql;
        command.Parameters.AddWithValue("@key",key);
        SqlDataAdapter sda = new SqlDataAdapter();
        sda.SelectCommand = command;
        DataSet ds = new DataSet();
        sda.Fill(ds);
        return ds;
    }
    public void addQualityControl(string imageUrl)
    {
        string sql = "insert into qc(imageUrl) values(@imageUrl)";
        SqlCommand command = connection.CreateCommand();
        command.CommandText = sql;
        command.Parameters.AddWithValue("@imageUrl",imageUrl);
        command.ExecuteNonQuery();
    }

    public DataSet getQualityControl()
    {
        string sql = "select * from qc";
        SqlCommand command = connection.CreateCommand();
        command.CommandText = sql;
        DataSet ds = new DataSet();
        SqlDataAdapter sda = new SqlDataAdapter();
        sda.SelectCommand = command;
        sda.Fill(ds);
        return ds;
    }

    public void deleteQualityControl(int qid)
    {
        string sql = "delete from qc where qid = @qid";
        SqlCommand command = connection.CreateCommand();
        command.CommandText = sql;
        command.Parameters.AddWithValue("@qid",qid);
        command.ExecuteNonQuery();
    }

    public void modifyNews(int newsId, string content_cn, string content_en)
    {
        string sql = "update news set newsContent_cn = @ccn,newsContent_en = @cen where newsId = @nid";
        SqlCommand command = connection.CreateCommand();
        command.Parameters.AddWithValue("@ccn",content_cn);
        command.Parameters.AddWithValue("@cen",content_en);
        command.Parameters.AddWithValue("@nid",newsId);
        command.CommandText = sql;
        command.ExecuteNonQuery();
    }
}
